package tsli.acm.ctrl;

import java.io.IOException;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import tsli.acm.biz.RAComponentAPIMethod;
import tsli.acm.dao.SFCAgentStructureDAO;
import tsli.acm.dao.SFCCommissionNegativeDAO;
import tsli.acm.dao.SFCCommissionPositiveDAO;
import tsli.acm.database.Database;

/**
 * Servlet implementation class ACM1202Srvl
 */
public class ACM1202Srvl extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ACM1202Srvl() {
        super();
    }
    
	public void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		if (request.getParameter("process_type").equals("getDataTable")) {
			getDataTable(request, response);
		} else if (request.getParameter("process_type").equals("getDetailsRA")) {
			getDetailsRA(request, response);
		} else if (request.getParameter("process_type").equals("getPopUpDetails")) {
			getPopUpDetails(request, response);
		}
	}
	
	@SuppressWarnings("unchecked")
	private void getDetailsRA (HttpServletRequest request, HttpServletResponse response) throws IOException {
		String vaDate = request.getParameter("paDate");
		String vaRecruiterId = request.getParameter("paRecruiterNumber");
		DecimalFormat df = new DecimalFormat("#,##0.00");
		Database db = new Database();
		SFCAgentStructureDAO voSFCASManagement = new SFCAgentStructureDAO(db);
		SFCCommissionPositiveDAO voSFCComPosManagement = new SFCCommissionPositiveDAO(db);
		SFCCommissionNegativeDAO voSFCComNegManagement = new SFCCommissionNegativeDAO(db);
		String[][] vaFYPForFreelook = voSFCComNegManagement.findFYPForCalRA(vaDate);
		String[] vaEmployeeId = voSFCASManagement.findNewEmployee(vaRecruiterId, vaDate);
		double[] vnFYP = new double[6];
		String vaNegative = "";
		// calculate ทุกๆเดือนของ employee แต่ละคน
		for (int y = 0; y < vaEmployeeId.length; y++) {
			// 0 = CLOS_YM, 1 = FYP, 2 = PO_NO, 3 = PE_NO_COL, 4 = FST_ENCO_MMS
			String[][] vaSumFYPInEachMonth = voSFCComPosManagement.findFYPForCalRA(vaEmployeeId[y], vaDate);
			// ถ้า 201411 - 201411 = 0 คือเดือน 6
			for(int z = 0; z < vaSumFYPInEachMonth.length; z++) {
				int vnMMS = Integer.parseInt(vaSumFYPInEachMonth[z][4]);
				for (int q = 0; q < vaFYPForFreelook.length; q++) {
					if (vaFYPForFreelook[q][2].equals(vaSumFYPInEachMonth[z][2])) {
						if (vaNegative.equals("")) 
							vaNegative += "<br>หมายเหตุ : ";
						vaNegative += "มีการยกเลิกกรมธรรม์จำนวนเงิน " + df.format(Double.parseDouble(vaFYPForFreelook[q][1])) + 
								" บาท ได้มาในเดือน/ปี: " + vaSumFYPInEachMonth[z][0].substring(4, 6) + "/" + vaSumFYPInEachMonth[z][0].substring(0, 4) + 
								" และถูกยกเลิกในเดือน/ปี: " + vaFYPForFreelook[q][0].substring(4, 6) + "/" + vaFYPForFreelook[q][0].substring(0, 4) + "<br>";
						if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 0) {
							vnFYP[vnMMS - 1] -= Double.parseDouble(vaFYPForFreelook[q][1]);
						// 201411 - 201410 = 1 คือเดือน 5
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 1) {
							vnFYP[vnMMS - 2] -= Double.parseDouble(vaFYPForFreelook[q][1]);
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 2) {
							vnFYP[vnMMS - 3] -= Double.parseDouble(vaFYPForFreelook[q][1]);
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 3) {
							vnFYP[vnMMS - 4] -= Double.parseDouble(vaFYPForFreelook[q][1]);
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 4) {
							vnFYP[vnMMS - 5] -= Double.parseDouble(vaFYPForFreelook[q][1]);
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 5) {
							vnFYP[vnMMS - 6] -= Double.parseDouble(vaFYPForFreelook[q][1]);
						}
					}
				}
				if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 0) {
					vnFYP[vnMMS - 1] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
				// 201411 - 201410 = 1 คือเดือน 5
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 1) {
					vnFYP[vnMMS - 2] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 2) {
					vnFYP[vnMMS - 3] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 3) {
					vnFYP[vnMMS - 4] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 4) {
					vnFYP[vnMMS - 5] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 5) {
					vnFYP[vnMMS - 6] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
				}				
			}
		}
		
		double vnSum = 0;
		double[] vaShowSum = new double[6];
		String[] vaTmpShowSum = new String[6];
		double[] vaPercent = new double[6];
		boolean vbStatus = false;
		for (int y = 0; y < 6; y++) {
			vnSum += vnFYP[y];
			if (vnSum < 30000) { // ถ้าน้อยกว่า 30,000
				vaShowSum[y] = vnSum;
			} else if (vnSum >= 30000 && vbStatus == false) { // ถ้ามากกว่าหรือเท่ากับ 30,000 ครั้งแรก
				vaShowSum[y] = vnSum;
				vbStatus = true;
			} else { // มากกว่า 30,000
				vaShowSum[y] = -1;
			}
		}
		// set accumulate FYP-C
		for (int y = 0; y < 6; y++) {
			if (vaShowSum[y] == -1) {
				vaTmpShowSum[y] = "";
			} else {
				vaTmpShowSum[y] = df.format(vaShowSum[y]);
			}
		}
		// set percent
		for (int y = 0; y < 6; y++) {
			if (vaShowSum[y] == -1 || vaShowSum[y] >= 30000) {
				vaPercent[y] = 5;
			} else {
				vaPercent[y] = 0;
			}
		}
		// RA to pay
		double[] vnSumRAEachRow = new double[6]; // RA ที่จ่ายแต่ละเดือน
		double vnSumRA = 0; // RA ที่จ่ายรวมทั้งหมด
		for (int y = 0; y < 6; y++) {
			if (vaShowSum[y] == -1) {
				//vnSumRA += vnFYP[y]*0.05;
				vnSumRAEachRow[y] = vnFYP[y]*0.05;
			} else if (vaShowSum[y] >= 30000) {
				vnSumRAEachRow[y] = vaShowSum[y]*0.05;
			} else {
				vnSumRAEachRow[y] = 0;
			}
		}
		for (int y = 0; y < 6; y++) {
			vnSumRA += vnSumRAEachRow[y];
		}
		PrintWriter voOut = response.getWriter();
		String vaHtml = "<table class='table-list'>" +
				"<tr><th width='15%'>เดือนที่</th><th width='10%'>1</th><th width='10%'>2</th><th width='10%'>3</th><th width='10%'>4</th> " +
				"<th width='10%'>5</th><th width='10%'>6</th><th width='8%'>รวม</th></tr><tr class='odd'><td > FYP-C ของ FC ใหม่ </td> " +
				"<td style='text-align:right'>" + df.format(vnFYP[0]) + 
				" <i class='fa fa-search' title='ดูรายละเอียด' onclick='showFYPDetails(\"" + vaRecruiterId + "\", \"" + 1 + "\", \"" + vaDate + "\");'></i></td> " +
				"<td style='text-align:right'>" + df.format(vnFYP[1]) + 
				" <i class='fa fa-search' title='ดูรายละเอียด' onclick='showFYPDetails(\"" + vaRecruiterId + "\", \"" + 2 + "\", \"" + vaDate + "\");'></i></td> " +
				"<td style='text-align:right'>" + df.format(vnFYP[2]) + 
				" <i class='fa fa-search' title='ดูรายละเอียด' onclick='showFYPDetails(\"" + vaRecruiterId + "\", \"" + 3 + "\", \"" + vaDate + "\");'></i></td> " +
				"<td style='text-align:right'>" + df.format(vnFYP[3]) + 
				" <i class='fa fa-search' title='ดูรายละเอียด' onclick='showFYPDetails(\"" + vaRecruiterId + "\", \"" + 4 + "\", \"" + vaDate + "\");'></i></td> " +
				"<td style='text-align:right'>" + df.format(vnFYP[4]) + 
				" <i class='fa fa-search' title='ดูรายละเอียด' onclick='showFYPDetails(\"" + vaRecruiterId + "\", \"" + 5 + "\", \"" + vaDate + "\");'></i></td> " +
				"<td style='text-align:right'>" + df.format(vnFYP[5]) + 
				" <i class='fa fa-search' title='ดูรายละเอียด' onclick='showFYPDetails(\"" + vaRecruiterId + "\", \"" + 6 + "\", \"" + vaDate + "\");'></i></td> " +
				"<td style='text-align:right'>" + df.format(vnSum) + "</td> " +
				"</tr><tr class='even'><td > รวม FYP-C<br> (สะสม 30,000) </td> " +
				"<td style='text-align:right'>" + vaTmpShowSum[0] + "</td> " +
				"<td style='text-align:right'>" + vaTmpShowSum[1] + "</td> " +
				"<td style='text-align:right'>" + vaTmpShowSum[2] + "</td> " +
				"<td style='text-align:right'>" + vaTmpShowSum[3] + "</td> " +
				"<td style='text-align:right'>" + vaTmpShowSum[4] + "</td> " +
				"<td style='text-align:right'>" + vaTmpShowSum[5] + "</td> " +
				"<td style='text-align:right'></td> " +
				"</tr><tr class='odd'><td > อัตราจ่าย </td> " + 
				"<td style='text-align:right'>" + vaPercent[0] + "%</td> " +
				"<td style='text-align:right'>" + vaPercent[1] + "%</td> " +
				"<td style='text-align:right'>" + vaPercent[2] + "%</td> " +
				"<td style='text-align:right'>" + vaPercent[3] + "%</td> " +
				"<td style='text-align:right'>" + vaPercent[4] + "%</td> " +
				"<td style='text-align:right'>" + vaPercent[5] + "%</td> " +
				"<td style='text-align:right'></td> " +
				"</tr><tr class='even'><td > รางวัลการชักชวน (RA) </td> " +
				"<td style='text-align:right'>" + df.format(vnSumRAEachRow[0]) + "</td> " +
				"<td style='text-align:right'>" + df.format(vnSumRAEachRow[1]) + "</td> " +
				"<td style='text-align:right'>" + df.format(vnSumRAEachRow[2]) + "</td> " +
				"<td style='text-align:right'>" + df.format(vnSumRAEachRow[3]) + "</td> " +
				"<td style='text-align:right'>" + df.format(vnSumRAEachRow[4]) + "</td> " +
				"<td style='text-align:right'>" + df.format(vnSumRAEachRow[5]) + "</td> " +
				"<td style='text-align:right'>" + df.format(vnSumRA) + "</td> " +
				"</tr></table>" + vaNegative;
		
		JSONObject voObject = new JSONObject();
		voObject.put("detailsTableRA", vaHtml);
		voOut.print(voObject.toJSONString());
		db.close();
		voOut.close();
	}
	
    @SuppressWarnings("unchecked")
    private void getDataTable (HttpServletRequest request, HttpServletResponse response) throws IOException {
    	DecimalFormat df = new DecimalFormat("#,##0.00");
    	// columns for sorting data
    	int vnStart = Integer.parseInt(request.getParameter("iDisplayStart")); // 0
    	int vnAmount = Integer.parseInt(request.getParameter("iDisplayLength")); // 10
    	String vaDraw = request.getParameter("sEcho");
    	String vaMonth = request.getParameter("sSearch_0").trim();
		String vaYear = request.getParameter("sSearch_1").trim();
    	// get data from database
    	int vnTotal = 0;
    	int vnTotalAfterFilter = 0;
    	List<Map<String, String>> voResult =  new ArrayList<Map<String, String>>();
    	String vaDate = vaYear + vaMonth;
    	String[] vaRecruiterName = null;
    	Database db = new Database();
    	SFCAgentStructureDAO voSFCASManagement = new SFCAgentStructureDAO(db);
    	RAComponentAPIMethod voRAAPI = new RAComponentAPIMethod();
		String[] vaRecruiterId = voSFCASManagement.findRecruiterIdBetweenOneToSix(vaDate);
    	try {
    	   	vnTotal = vaRecruiterId.length;
    		if (vnAmount == -1) {
    			for (int i = 0; i < vaRecruiterId.length; i++) {
    				vaRecruiterName = voSFCASManagement.findNameById(vaRecruiterId[i]);
    				Map<String, String> voTmp = new HashMap<String, String>();
    				voTmp.put("TITLE_NAME", vaRecruiterName[0]);
    				voTmp.put("NAME", vaRecruiterName[1]);
    				voTmp.put("SURNAME", vaRecruiterName[2]);
    				voTmp.put("CAL", df.format(voRAAPI.CalRA(vaDate, vaRecruiterId[i])));
    				voResult.add(voTmp);
    			}
    		    vnTotalAfterFilter = vnTotal;
    		} else {
    			if (vaRecruiterId.length != 0) {
    				for (int i = vnStart; i < (vnStart + vnAmount); i++) {
    					vaRecruiterName = voSFCASManagement.findNameById(vaRecruiterId[i]);
    					Map<String, String> voTmp = new HashMap<String, String>();
    					voTmp.put("TITLE_NAME", vaRecruiterName[0]);
    					voTmp.put("NAME", vaRecruiterName[1]);
    					voTmp.put("SURNAME", vaRecruiterName[2]);
    					voTmp.put("CAL", df.format(voRAAPI.CalRA(vaDate, vaRecruiterId[i])));
    					voResult.add(voTmp);
    					if (i == (vaRecruiterId.length - 1)) 
    						break;
    				}
    			}
    			vnTotalAfterFilter = vnTotal;
    		}
    	} finally {
    		db.close();
    	}  
    	// prepare data to show in dataTable
    	PrintWriter voOut = response.getWriter();
    	// set sequence
    	int[] vnSequence = new int[voResult.size()];
    	for (int i = 1; i <= voResult.size(); i++)
    		vnSequence[i - 1] = i + vnStart;
    	try {
    	   	JSONObject voObject = new JSONObject();
    	    JSONArray voListData = new JSONArray();
    	    for (int i = 0; i < voResult.size(); i++) {
    	    	Map<String, String> voTmp = voResult.get(i);	
    			JSONArray voArray = new JSONArray();
    			voArray.add(vnSequence[i]);
    			voArray.add(vaRecruiterId[i + vnStart]);
    			voArray.add((voTmp.get("TITLE_NAME") + " " + voTmp.get("NAME") + " " + voTmp.get("SURNAME")));
    			voArray.add(voTmp.get("CAL"));
    		    // data for button's action
    			voArray.add(vaRecruiterId[i + vnStart] + "/" 
    		    + (voTmp.get("TITLE_NAME") + " " + voTmp.get("NAME") + " " + voTmp.get("SURNAME")) + "/"
    		    + vaDate);
    			// adding array data into list
    		    voListData.add(voArray);
    		}
    		voObject.put("sEcho", Integer.parseInt(vaDraw));
    		voObject.put("iTotalRecords", vnTotal);
    		voObject.put("iTotalDisplayRecords", vnTotalAfterFilter);
    		voObject.put("aaData", voListData);
    		voOut.print(voObject.toJSONString());
    	} finally {
    			voOut.close();
    	}
    }
    
    @SuppressWarnings("unchecked")
	private void getPopUpDetails (HttpServletRequest request, HttpServletResponse response) throws IOException {
		DecimalFormat df = new DecimalFormat("#,##0.00");
    	// columns for sorting data
    	int vnStart = Integer.parseInt(request.getParameter("iDisplayStart")); // 0
    	int vnAmount = Integer.parseInt(request.getParameter("iDisplayLength")); // 10
    	String vaDraw = request.getParameter("sEcho");
    	String vaRecruiterId = request.getParameter("sSearch_0").trim();
		String vaMonth = request.getParameter("sSearch_1").trim();
		String vaDate = request.getParameter("sSearch_2").trim();
    	// get data from database
    	int vnTotal = 0;
    	int vnTotalAfterFilter = 0;
    	List<Map<String, String>> voResult =  new ArrayList<Map<String, String>>();
    	
    	Database db = new Database();
    	SFCAgentStructureDAO voSFCASManagement = new SFCAgentStructureDAO(db);
		SFCCommissionPositiveDAO voSFCComPosManagement = new SFCCommissionPositiveDAO(db);
		SFCCommissionNegativeDAO voSFCComNegManagement = new SFCCommissionNegativeDAO(db);
		// 0 = CLOS_YM, 1 = SUM_FYP, 2 = PO_NO, 3 = PE_NO_COL
		String[][] vaFYPForFreelook = voSFCComNegManagement.findFYPForCalRA(vaDate);
		
		String[] vaEmployeeId = voSFCASManagement.findNewEmployee(vaRecruiterId, vaDate);
		double[] vnFYP = new double[6];
		// calculate ทุกๆเดือนของ employee แต่ละคน
		for (int y = 0; y < vaEmployeeId.length; y++) {
			// 0 = CLOS_YM, 1 = FYP, 2 = PO_NO, 3 = PE_NO_COL, 4 = FST_ENCO_MMS
			String[][] vaSumFYPInEachMonth = voSFCComPosManagement.findFYPForCalRA(vaEmployeeId[y], vaDate);
			// ถ้า 201411 - 201411 = 0 คือเดือน 6
			for(int z = 0; z < vaSumFYPInEachMonth.length; z++) {
				int vnMMS = Integer.parseInt(vaSumFYPInEachMonth[z][4]);
				
				if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 0) {
					vnFYP[vnMMS - 1] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
					if (vnMMS == Integer.parseInt(vaMonth)) {
						Map<String, String> voTmp = new HashMap<String, String>();
	    				voTmp.put("CLOS_YM", vaSumFYPInEachMonth[z][0]);
	    				voTmp.put("PE_NO_COL", vaSumFYPInEachMonth[z][3]);
	    				voTmp.put("PO_NO", vaSumFYPInEachMonth[z][2]);
	    				voTmp.put("FYP", vaSumFYPInEachMonth[z][1]);
	    				voResult.add(voTmp);
					}
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 1) {
					vnFYP[vnMMS - 2] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
					if ((vnMMS - 1) == Integer.parseInt(vaMonth)) {
						Map<String, String> voTmp = new HashMap<String, String>();
	    				voTmp.put("CLOS_YM", vaSumFYPInEachMonth[z][0]);
	    				voTmp.put("PE_NO_COL", vaSumFYPInEachMonth[z][3]);
	    				voTmp.put("PO_NO", vaSumFYPInEachMonth[z][2]);
	    				voTmp.put("FYP", vaSumFYPInEachMonth[z][1]);
	    				voResult.add(voTmp);
					}
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 2) {
					vnFYP[vnMMS - 3] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
					if ((vnMMS - 2) == Integer.parseInt(vaMonth)) {
						Map<String, String> voTmp = new HashMap<String, String>();
	    				voTmp.put("CLOS_YM", vaSumFYPInEachMonth[z][0]);
	    				voTmp.put("PE_NO_COL", vaSumFYPInEachMonth[z][3]);
	    				voTmp.put("PO_NO", vaSumFYPInEachMonth[z][2]);
	    				voTmp.put("FYP", vaSumFYPInEachMonth[z][1]);
	    				voResult.add(voTmp);
					}
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 3) {
					vnFYP[vnMMS - 4] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
					if ((vnMMS - 3) == Integer.parseInt(vaMonth)) {
						Map<String, String> voTmp = new HashMap<String, String>();
	    				voTmp.put("CLOS_YM", vaSumFYPInEachMonth[z][0]);
	    				voTmp.put("PE_NO_COL", vaSumFYPInEachMonth[z][3]);
	    				voTmp.put("PO_NO", vaSumFYPInEachMonth[z][2]);
	    				voTmp.put("FYP", vaSumFYPInEachMonth[z][1]);
	    				voResult.add(voTmp);
					}
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 4) {
					vnFYP[vnMMS - 5] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
					if ((vnMMS - 4) == Integer.parseInt(vaMonth)) {
						Map<String, String> voTmp = new HashMap<String, String>();
	    				voTmp.put("CLOS_YM", vaSumFYPInEachMonth[z][0]);
	    				voTmp.put("PE_NO_COL", vaSumFYPInEachMonth[z][3]);
	    				voTmp.put("PO_NO", vaSumFYPInEachMonth[z][2]);
	    				voTmp.put("FYP", vaSumFYPInEachMonth[z][1]);
	    				voResult.add(voTmp);
					}
				} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 5) {
					vnFYP[vnMMS - 6] += Double.parseDouble(vaSumFYPInEachMonth[z][1]);
					if ((vnMMS - 5) == Integer.parseInt(vaMonth)) {
						Map<String, String> voTmp = new HashMap<String, String>();
	    				voTmp.put("CLOS_YM", vaSumFYPInEachMonth[z][0]);
	    				voTmp.put("PE_NO_COL", vaSumFYPInEachMonth[z][3]);
	    				voTmp.put("PO_NO", vaSumFYPInEachMonth[z][2]);
	    				voTmp.put("FYP", vaSumFYPInEachMonth[z][1]);
	    				voResult.add(voTmp);
					}
				}
				
				for (int q = 0; q < vaFYPForFreelook.length; q++) {
					if (vaFYPForFreelook[q][2].equals(vaSumFYPInEachMonth[z][2])) {
						if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 0) {
							vnFYP[vnMMS - 1] -= Double.parseDouble(vaFYPForFreelook[q][1]);
							if (vnMMS == Integer.parseInt(vaMonth)) {
								Map<String, String> voTmp = new HashMap<String, String>();
			    				voTmp.put("CLOS_YM", vaFYPForFreelook[q][0]);
			    				voTmp.put("PE_NO_COL", vaFYPForFreelook[q][3]);
			    				voTmp.put("PO_NO", vaFYPForFreelook[q][2]);
			    				voTmp.put("FYP", "-" + vaFYPForFreelook[q][1]);
			    				voResult.add(voTmp);
							}
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 1) {
							vnFYP[vnMMS - 2] -= Double.parseDouble(vaFYPForFreelook[q][1]);
							if ((vnMMS - 1) == Integer.parseInt(vaMonth)) {
								Map<String, String> voTmp = new HashMap<String, String>();
			    				voTmp.put("CLOS_YM", vaFYPForFreelook[q][0]);
			    				voTmp.put("PE_NO_COL", vaFYPForFreelook[q][3]);
			    				voTmp.put("PO_NO", vaFYPForFreelook[q][2]);
			    				voTmp.put("FYP", "-" + vaFYPForFreelook[q][1]);
			    				voResult.add(voTmp);
							}
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 2) {
							vnFYP[vnMMS - 3] -= Double.parseDouble(vaFYPForFreelook[q][1]);
							if ((vnMMS - 2) == Integer.parseInt(vaMonth)) {
								Map<String, String> voTmp = new HashMap<String, String>();
			    				voTmp.put("CLOS_YM", vaFYPForFreelook[q][0]);
			    				voTmp.put("PE_NO_COL", vaFYPForFreelook[q][3]);
			    				voTmp.put("PO_NO", vaFYPForFreelook[q][2]);
			    				voTmp.put("FYP", "-" + vaFYPForFreelook[q][1]);
			    				voResult.add(voTmp);
							}
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 3) {
							vnFYP[vnMMS - 4] -= Double.parseDouble(vaFYPForFreelook[q][1]);
							if ((vnMMS - 3) == Integer.parseInt(vaMonth)) {
								Map<String, String> voTmp = new HashMap<String, String>();
			    				voTmp.put("CLOS_YM", vaFYPForFreelook[q][0]);
			    				voTmp.put("PE_NO_COL", vaFYPForFreelook[q][3]);
			    				voTmp.put("PO_NO", vaFYPForFreelook[q][2]);
			    				voTmp.put("FYP", "-" + vaFYPForFreelook[q][1]);
			    				voResult.add(voTmp);
							}
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 4) {
							vnFYP[vnMMS - 5] -= Double.parseDouble(vaFYPForFreelook[q][1]);
							if ((vnMMS - 4) == Integer.parseInt(vaMonth)) {
								Map<String, String> voTmp = new HashMap<String, String>();
			    				voTmp.put("CLOS_YM", vaFYPForFreelook[q][0]);
			    				voTmp.put("PE_NO_COL", vaFYPForFreelook[q][3]);
			    				voTmp.put("PO_NO", vaFYPForFreelook[q][2]);
			    				voTmp.put("FYP", "-" + vaFYPForFreelook[q][1]);
			    				voResult.add(voTmp);
							}
						} else if (Integer.parseInt(vaDate) - Integer.parseInt(vaSumFYPInEachMonth[z][0]) == 5) {
							vnFYP[vnMMS - 6] -= Double.parseDouble(vaFYPForFreelook[q][1]);
							if ((vnMMS - 5) == Integer.parseInt(vaMonth)) {
								Map<String, String> voTmp = new HashMap<String, String>();
			    				voTmp.put("CLOS_YM", vaFYPForFreelook[q][0]);
			    				voTmp.put("PE_NO_COL", vaFYPForFreelook[q][3]);
			    				voTmp.put("PO_NO", vaFYPForFreelook[q][2]);
			    				voTmp.put("FYP", "-" + vaFYPForFreelook[q][1]);
			    				voResult.add(voTmp);
							}
						}
					}
				}
				
			}
		}
		
		List<Map<String, String>> voResult1 =  new ArrayList<Map<String, String>>();
    	try {
    	   	vnTotal = voResult.size();
    		if (vnAmount == -1) {
    			for (int i = 0; i < voResult.size(); i++) {
    				Map<String, String> voTmp = voResult.get(i);
    				Map<String, String> voTmp1 = new HashMap<String, String>();
    				voTmp1.put("CLOS_YM", voTmp.get("CLOS_YM"));
    				voTmp1.put("PE_NO_COL", voTmp.get("PE_NO_COL"));
    				voTmp1.put("PO_NO", voTmp.get("PO_NO"));
    				voTmp1.put("FYP", voTmp.get("FYP"));
    				voResult1.add(voTmp1);
    			}
    		    vnTotalAfterFilter = vnTotal;
    		} else {
    			if (voResult.size() != 0) {
    				for (int i = vnStart; i < (vnStart + vnAmount); i++) {
    					Map<String, String> voTmp = voResult.get(i);
        				Map<String, String> voTmp1 = new HashMap<String, String>();
        				voTmp1.put("CLOS_YM", voTmp.get("CLOS_YM"));
        				voTmp1.put("PE_NO_COL", voTmp.get("PE_NO_COL"));
        				voTmp1.put("PO_NO", voTmp.get("PO_NO"));
        				voTmp1.put("FYP", voTmp.get("FYP"));
    					voResult1.add(voTmp1);
    					if (i == (voResult.size() - 1)) 
    						break;
    				}
    			}
    			vnTotalAfterFilter = vnTotal;
    		}
    	} finally {
    		db.close();
    	}
    	// prepare data to show in dataTable
    	PrintWriter voOut = response.getWriter();
    	// set sequence
    	int[] vnSequence = new int[voResult1.size()];
    	for (int i = 1; i <= voResult1.size(); i++)
    		vnSequence[i - 1] = i + vnStart;
    	try {
    	   	JSONObject voObject = new JSONObject();
    	    JSONArray voListData = new JSONArray();
    	    for (int i = 0; i < voResult1.size(); i++) {
    	    	Map<String, String> voTmp = voResult1.get(i);	
    			JSONArray voArray = new JSONArray();
    			voArray.add(vnSequence[i]);
    			voArray.add(voTmp.get("CLOS_YM"));
    			voArray.add(voTmp.get("PE_NO_COL"));
    			voArray.add(voTmp.get("PO_NO"));
    			voArray.add(df.format(Double.parseDouble(voTmp.get("FYP"))));
    			// adding array data into list
    		    voListData.add(voArray);
    		}
    		voObject.put("sEcho", Integer.parseInt(vaDraw));
    		voObject.put("iTotalRecords", vnTotal);
    		voObject.put("iTotalDisplayRecords", vnTotalAfterFilter);
    		voObject.put("aaData", voListData);
    		voOut.print(voObject.toJSONString());
    	} finally {
    			voOut.close();
    	}
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		processRequest(request, response);
	}

}
